package com.example.jdbc.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.example.jdbc.domain.Book;

/**
 * Project:spring-boot-jdbc
 * <p>
 * Title:BookService.java
 * <p>
 * Description:
 * <p>
 * Copyrigth:Jiang Software LTD.co Copyright (c) 2022
 * <p>
 * 
 * @version 1.0
 * @since 1.8
 * @author jiang
 * @history 2022 jiang create
 */
@Service
public class BookService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int create(int bookId, String bookName, String bookAddress) {
		String sql = "insert into t_book(bookId,bookName,bookAddress) values(?, ?,?)";
		return jdbcTemplate.update(sql, bookId, bookName, bookAddress);
	}

	public int create(Book book) {
		String sql = "insert into t_book(bookId,bookName,bookAddress) values(?, ?,?)";
		Object[] params = new Object[] { book.getBookId(), book.getBookName(), book.getBookAddress() };
		return jdbcTemplate.update(sql, params);
	}

	public int update(int bookId, String bookName, String bookAddress) {
		String sql = "update t_book set bookName=?,bookAddress=? where bookId=?";
		return jdbcTemplate.update(sql, bookName, bookAddress, bookId);
	}

	public int update(Book book) {
		String sql = "update t_book set bookName=?,bookAddress=? where bookId=?";
		Object[] params = new Object[] { book.getBookName(), book.getBookAddress(), book.getBookId() };
		return jdbcTemplate.update(sql, params);
	}

	public List<Book> findBookByName(String bookName) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookName = ?";
		List<Book> books = jdbcTemplate.query(sql, (resultSet, i) -> {
			Book book = new Book();
			book.setBookId(resultSet.getInt("bookId"));
			book.setBookName(resultSet.getString("bookName"));
			book.setBookAddress(resultSet.getString("bookAddress"));
			return book;
		}, bookName);
		return books;
	}

	public List<Book> findBookByName2(String bookName) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookName = ?";
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> books = jdbcTemplate.query(sql, accountRowMapper, bookName);
		return books;
	}

	public Book findBookById(int bookId) {
		String sql = "select * from t_book where bookId = ?";
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		Book book = jdbcTemplate.queryForObject(sql, accountRowMapper, bookId);
		return book;
	}

	// 自定义映射使用
	public Book findBookById2(int bookId) {
		String sql = "select * from t_book where bookId = ?";
		BookRowMapper mapper = new BookRowMapper();
		Book book = jdbcTemplate.queryForObject(sql, mapper, bookId);
		return book;
	}

	public Book findBook(Book book) {
		String sql = "select bookId,bookName,bookAddress from t_book where bookId = ? or bookName=? or bookAddress=?";
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		Object[] params = new Object[] { book.getBookName(), book.getBookAddress(), book.getBookId() };
		return jdbcTemplate.queryForObject(sql, accountRowMapper, params);
	}

	public List<Book> findAllBooks() {
		String sql = "select bookId,bookName,bookAddress from t_book";
		RowMapper<Book> accountRowMapper = new BeanPropertyRowMapper<>(Book.class);
		List<Book> list = jdbcTemplate.query(sql, accountRowMapper);
		return list;
	}

	public int deleteById(int bookId) {
		String sql = "delete from t_book where bookId = ?";
		return jdbcTemplate.update(sql, bookId);
	}

	public int getAllBooksCount() {
		String sql = "select count(1) from t_book";
		return jdbcTemplate.queryForObject(sql, Integer.class);
	}

	public int deleteAllBooks() {
		String sql = "delete from t_book";
		return jdbcTemplate.update(sql);
	}

}
